package com.tinyideatech.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tinyideatech.system.entity.MallOrder;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;

/**
 * @author zhangy-5
 * @date 2019-08-26
 */
@Repository
public interface MallOrderMapper extends BaseMapper<MallOrder> {

    @Select("SELECT SUM(total_price) as total FROM mall_order WHERE payment_result=1")
    Double consume();

    @Select("SELECT SUM(total_price) as total FROM mall_order WHERE payment_result=1 and YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(#{date},1)")
    Double sumByMonth(Date date);

    @Select("SELECT a.num1 - b.num2 as plus from\n" +
            "(SELECT COUNT(order_id) as num1 from mall_order where YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(#{date},1))a,\n" +
            "((SELECT COUNT(order_id) as num2 from mall_order where YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(#{date},1)-1))b")
    int singlePlus(Date date);


    // @Select("SELECT COUNT(DISTINCT device_id) as num from mall_order where DATE_FORMAT(create_time,'%Y%m' ) = DATE_FORMAT( CURDATE(), '%Y%m')")
    @Select("select count(DISTINCT device_id) as num from mall_order where year(create_time)=#{year} and MONTH(create_time)=#{month}")
    int countByMonth(int year, int month);

    @Select("select COUNT(order_id) as num from mall_order where TO_DAYS(create_time) = TO_DAYS(#{date}) and payment_result=1")
    int countByToday(Date date);

    @Select("SELECT SUM(total_price) as total FROM mall_order WHERE user_id=#{id}")
    double consumeById(long id);
}